﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;

using ECS.DAL;
using ECS.DAL.Entity;
namespace ECS.DAL.Component
{
    public class UserAuthorizationComponent : BaseSQLModel
    {
        #region private properties
        //grp.user_group_id, 
                    //data.AppName = base.GetString(1, reader);
                    //data.UserId = base.GetString(0, reader);
                    //data.Process = base.GetString(4, reader);
                    //data.ParentMenuId = base.GetString(2, reader);
                    //data.ResourceMasterId = base.GetString(7, reader);
                    //data.MenuText = base.GetString(3, reader);
                    //data.MenuName = base.GetString(10, reader);
                    //data.IsActive = base.GetBoolean(11, reader);
        private const string SQL_QUERY = "SELECT app.app_id, u.[user_id], menu.process, menu.parent_menu_id, " +
                                        " menu.resource_master_id, dbo.usf_get_locale_text_value(@AppId, resource_master_id, @locale) as 'MenuText', " +
                                        " menu.menu_name, menu.is_active " +
                                        " FROM [dbo].[t_com_user] u " +
                                        " INNER JOIN [dbo].[t_com_user_application] app ON u.[user_id] = app.[user_id] " +
                                        " INNER JOIN [dbo].[t_com_user_group] grp ON app.user_group_id = grp.user_group_id AND " +
                                        " app.app_id = grp.app_id " +
                                        " INNER JOIN [dbo].[t_com_menu] menu ON grp.menu_id = menu.parent_menu_id AND " +
                                        " grp.app_id = menu.app_id " +
                                        " WHERE app.app_id = @AppId AND u.[user_id] = @UserId ";

        private const string SQL_PROC_QUERY = "usp_SecManGetMenu";

        private const string SQL_INSERT = " ";

        private const string SQL_PROC_INSERT = "";

        private const string SQL_UPDATE = " ";

        private const string SQL_PROC_UPDATE = "";

        private const string SQL_DELETE = " ";
        #endregion

        #region public properties
        /// <summary>
        /// Error result
        /// </summary>
        public ErrorInfo ErrorMessage { set; get; }
        #endregion

        #region constructor
        public UserAuthorizationComponent()
        {
            ErrorMessage = new ErrorInfo();
        }
        #endregion

        #region private methods

        #endregion

        #region public mehods
        public List<ObjectContext> GetUserMenu(ObjectContext param)
        {
            UserAuthorization parameter = (UserAuthorization)param;
            List<ObjectContext> items = new List<Entity.ObjectContext>();
            using (Entities entities = new Entities("con"))
            {
                StringBuilder sqlString = new StringBuilder();
                sqlString.Append(SQL_QUERY);
                sqlString.Replace("@UserId", SqlWithQoute(parameter.UserId));
                sqlString.Replace("@AppId", SqlWithQoute(parameter.AppId));
                sqlString.Replace("@locale", SqlWithoutQoute(parameter.Locale));
                entities.Open();
                DbCommand cmd = entities.CreateCommand(sqlString.ToString());
                cmd.CommandType = System.Data.CommandType.Text;
                DbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    UserAuthorization data = new UserAuthorization();
                    data.AppName = base.GetString(0, reader);
                    data.UserId = base.GetString(1, reader);
                    data.Process = base.GetString(2, reader);
                    data.ParentMenuId = base.GetString(3, reader);
                    data.ResourceMasterId = base.GetString(4, reader);
                    data.MenuText = base.GetString(5, reader);
                    data.MenuName = base.GetString(6, reader);
                    data.IsActive = !string.IsNullOrEmpty(base.GetString(7, reader)) && base.GetString(7, reader).Equals("YES") ? true : false;

                    data.NotChangeObject();
                    items.Add(data);
                }
            }
            return items;
        }
        /// <summary>
        /// get Eics setup data to list
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public List<ObjectContext> Search(ObjectContext parameter)
        {
            //UserId, AppId, Device, platform , intLocale
            //EXEC usp_SecManGetMenu @in_vchUserID, @in_vchApplicationID,@in_vchDevice, @in_vchPlatfor, @in_intLocale

            UserAuthorization param = (UserAuthorization)parameter;
            List<ObjectContext> items = new List<Entity.ObjectContext>();
            using (Entities entities = new Entities("con"))
            {
                StringBuilder sqlString = new StringBuilder();
                sqlString.Append(SQL_PROC_QUERY);
                entities.Open();
                DbCommand cmd = entities.CreateCommand(sqlString.ToString());
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", param.AppId));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", param.Device));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", param.UserId));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchPlatform", param.Platform));
                cmd.Parameters.Add(entities.CreateParameter("@in_intLocale", param.Locale));
                DbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    UserAuthorization data = new UserAuthorization();
                    data.Status = base.GetString(0, reader);
                    data.ErrorCode = base.GetString(1, reader);
                    data.Message = base.GetString(2, reader);
                    data.Locale = (int)base.GetInt(3, reader);
                    data.AppName = base.GetString(4, reader);
                    data.UserId = base.GetString(5, reader);
                    data.Process = base.GetString(6, reader);
                    data.ParentMenuId = base.GetString(7, reader);
                    data.ResourceMasterId = base.GetString(8, reader);
                    data.MenuText = base.GetString(9, reader);
                    data.MenuName = base.GetString(10, reader);
                    data.IsActive = base.GetBoolean(11, reader);
                    data.NotChangeObject();
                    items.Add(data);
                }
            }
            return items;
        }

        public override string GetInsertSQL(ObjectContext param)
        {
            /*@AppId, @SlipId, @SlipId, @DimensionNumber, @Length, @Weight, @Height, @PeiceCount, @Device, @UserId "; */
            UserAuthorization parameter = (UserAuthorization)param;
            StringBuilder sqlString = new StringBuilder();
            //insert command
            sqlString.Append(SQL_PROC_INSERT);
            //sqlString.Replace("@AppId", BaseSQLModel.SqlWithQoute(parameter.AppId));
            //sqlString.Replace("@SlipId", BaseSQLModel.SqlWithQoute(parameter.SlipId));
            //sqlString.Replace("@CommentTypeId", BaseSQLModel.SqlWithQoute(parameter.CommentTypeId));
            //sqlString.Replace("@CommentNumber", BaseSQLModel.SqlWithoutQoute(parameter.CommentNumber));
            //sqlString.Replace("@Value", BaseSQLModel.SqlWithQoute(parameter.Value));
            //sqlString.Replace("@Device", BaseSQLModel.SqlWithQoute(parameter.Device));
            //sqlString.Replace("@UserId", BaseSQLModel.SqlWithQoute(parameter.UserId));

            return sqlString.ToString();
        }

        public override string GetUpdateSQL(ObjectContext param)
        {
            UserAuthorization parameter = (UserAuthorization)param;
            StringBuilder sqlString = new StringBuilder();
            //sqlString.Append(SQL_UPDATE);
            sqlString.Append(SQL_PROC_UPDATE);
            //sqlString.Replace("@in_vchApplicationID", BaseSQLModel.SqlWithQoute(parameter.AppId));

            //sqlString.Replace("@in_vchSlipID", BaseSQLModel.SqlWithQoute(parameter.SlipId));
            //sqlString.Replace("@in_intCommentNumber", BaseSQLModel.SqlWithoutQoute(parameter.CommentNumber.ToString()));
            //sqlString.Replace("@in_vchCommentID", BaseSQLModel.SqlWithQoute(parameter.CommentId));
            //sqlString.Replace("@in_vchCommentTypeID", BaseSQLModel.SqlWithQoute(parameter.CommentTypeId));
            //sqlString.Replace("@in_vchValue", BaseSQLModel.SqlWithQoute(parameter.Value));

            //sqlString.Replace("@in_vchDevice", BaseSQLModel.SqlWithQoute(parameter.Device));
            //sqlString.Replace("@in_vchUserID", BaseSQLModel.SqlWithQoute(parameter.UserId));

            return sqlString.ToString();
        }

        public override string GetDeleteSQL(ObjectContext param)
        {
            UserAuthorization parameter = (UserAuthorization)param;
            StringBuilder sqlString = new StringBuilder();
            //insert command
            //sqlString.Append(SQL_DELETE);
            //sqlString.Replace("@setup_id", BaseSQLModel.SqlWithQoute(parameter.SetupId));
            return sqlString.ToString();
        }
        /// <summary>
        /// Insert to data source
        /// </summary>
        /// <param name="param"></param>
        public override void Insert(ObjectContext param)
        {
            DataSet ds = new DataSet();
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetInsertSQL(param));
                    DbDataAdapter adapter = entities.CreateDataAdapter(GetInsertSQL(param));
                    adapter.Fill(ds, "Result");
                    //entities.SetCommand(cmd);
                    //int m_iRecordChanged = entities.SaveChanges();
                    //if (m_iRecordChanged < 1)
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        ErrorMessage.Status = ds.Tables[0].Rows[0]["Status"].ToString();
                        ErrorMessage.ErrorCode = ds.Tables[0].Rows[0]["ErrorCode"].ToString();
                        ErrorMessage.Message = ds.Tables[0].Rows[0]["Message"].ToString();
                        ErrorMessage.ID = string.Empty;
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// update to datasource
        /// </summary>
        /// <param name="param"></param>
        public override void Update(ObjectContext param)
        {
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetUpdateSQL(param));
                    entities.SetCommand(cmd);
                    int m_iRecordChanged = entities.SaveChanges();
                    if (m_iRecordChanged < 1)
                    {
                        throw new Exception("comment record update fail.");
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// delete from datasource
        /// </summary>
        public override void Delete(ObjectContext param)
        {
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetDeleteSQL(param));
                    entities.SetCommand(cmd);
                    int m_iRecordChanged = entities.SaveChanges();
                    if (m_iRecordChanged < 0)
                    {
                        throw new Exception("comment record delete fail.");
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// special case for store procedure to update data that return result as record
        /// </summary>
        /// <param name="items"></param>
        public override void SaveChanges(List<ObjectContext> items)
        {
            UserAuthorization parameter = null;
            string sqlCmd = string.Empty;
            string textMessage = string.Empty;
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DataSet ds = new DataSet();
                    DbCommand cmd = entities.CreateCommand(sqlCmd);

                    DbDataAdapter adapter = entities.CreateDataAdapter(sqlCmd);
                    adapter.SelectCommand = cmd;
                    //cmd.CommandType = CommandType.StoredProcedure;
                    foreach (ObjectContext item in items)
                    {
                        parameter = (UserAuthorization)item;
                        switch (item.GetObjectSate())
                        {
                            case OBJECT_STATE.Insert:
                                sqlCmd = GetInsertSQL(item);
                                textMessage = "Insert ";
                                cmd.CommandText = sqlCmd;
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.Parameters.Clear();
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", parameter.AppId));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchSlipID", parameter.SlipId));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchCommentTypeID", parameter.CommentTypeId));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_intCommentNumber", parameter.CommentNumber));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchValue", parameter.Value));
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", parameter.Device));
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", parameter.UserId));
                                break;
                            case OBJECT_STATE.Update:
                                sqlCmd = GetUpdateSQL(item);
                                textMessage = "Update ";
                                cmd.CommandText = sqlCmd;
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.Parameters.Clear();
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", parameter.AppId));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchCommentID", parameter.CommentId));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchSlipID", parameter.SlipId));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchCommentTypeID", parameter.CommentTypeId));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_intCommentNumber", parameter.CommentNumber));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchValue", parameter.Value));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", parameter.Device));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", parameter.UserId));
                                break;
                            case OBJECT_STATE.Delete:
                                sqlCmd = GetDeleteSQL(item);
                                textMessage = "Delete ";
                                //cmd.CommandText = sqlCmd;
                                //cmd.CommandType = CommandType.StoredProcedure;
                                //cmd.Parameters.Clear();
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", parameter.AppId));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchCommentID", parameter.CommentId));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchSlipID", parameter.SlipId));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchCommentTypeID", parameter.CommentTypeId));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_intCommentNumber", parameter.CommentNumber));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchValue", parameter.Value));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", parameter.Device));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", parameter.UserId));
                                break;
                            default:
                                sqlCmd = string.Empty;
                                break;
                        }
                        if (!string.IsNullOrEmpty(sqlCmd))
                        {
                            adapter.Fill(ds);
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                //if (!ds.Tables[0].Rows[0]["Status"].ToString().Equals("PASS"))
                                //{
                                //    string errorMessage = string.Format("Status: {0} Error Code:{1}\r\nError:{2}", ds.Tables[0].Rows[0]["Status"].ToString(), ds.Tables[0].Rows[0]["ErrorCode"].ToString(), ds.Tables[0].Rows[0]["Message"].ToString());
                                //    throw new Exception(errorMessage);
                                //}
                                ErrorMessage.Status = ds.Tables[0].Rows[0]["Status"].ToString();
                                ErrorMessage.ErrorCode = ds.Tables[0].Rows[0]["ErrorCode"].ToString();
                                ErrorMessage.Message = ds.Tables[0].Rows[0]["Message"].ToString();
                            }
                            //item.NotChangeObject();
                        }
                    }
                }
                transaction.Complete();
                items.RemoveAll(o => o.GetObjectSate() == OBJECT_STATE.Delete);
                foreach (ObjectContext data in items)
                {
                    if (data.GetObjectSate() != OBJECT_STATE.None)
                    {
                        data.NotChangeObject();
                    }
                }
            }
        }
        #endregion
    }
}
